/* ***** BEGIN LICENSE BLOCK *****
 *   Version: MPL 1.1/GPL 2.0/LGPL 2.1
 *
 * The contents of this file are subject to the Mozilla Public License Version
 * 1.1 (the "License"); you may not use this file except in compliance with
 * the License. You may obtain a copy of the License at
 * http://www.mozilla.org/MPL/
 * 
 * Software distributed under the License is distributed on an "AS IS" basis,
 * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
 * for the specific language governing rights and limitations under the
 * License.
 *
 * The Original Code is sudoku.
 *
 * The Initial Developer of the Original Code is
 * Petr Blahoš.
 * Portions created by the Initial Developer are Copyright (C) 2008
 * the Initial Developer. All Rights Reserved.
 *
 * Contributor(s):
 *
 * Alternatively, the contents of this file may be used under the terms of
 * either the GNU General Public License Version 2 or later (the "GPL"), or
 * the GNU Lesser General Public License Version 2.1 or later (the "LGPL"),
 * in which case the provisions of the GPL or the LGPL are applicable instead
 * of those above. If you wish to allow use of your version of this file only
 * under the terms of either the GPL or the LGPL, and not to allow others to
 * use your version of this file under the terms of the MPL, indicate your
 * decision by deleting the provisions above and replace them with the notice
 * and other provisions required by the GPL or the LGPL. If you do not delete
 * the provisions above, a recipient may use your version of this file under
 * the terms of any one of the MPL, the GPL or the LGPL.
 * 
 * ***** END LICENSE BLOCK ***** */
EXPORTED_SYMBOLS = [
	"griddb",
	"GRID"
];

var consoleService = Components.classes["@mozilla.org/consoleservice;1"].getService(Components.interfaces.nsIConsoleService);
function LOG(msg) {
	consoleService.logStringMessage(msg);
}

function GRID(id, gid, grid, rating, dt, best, sum, count) {
	this.id = id;
	this.grid = grid;
	this.rating = rating;
	this.dt = dt;
	this.best = best;
	this.sum = sum;
	this.count = count;
}

GRID.prototype = {
set_date: function() {
	var d = new Date();
	this.dt = d.getFullYear()<<16 | (d.getMonth()+1)<<8 | d.getDate();
},
get_date: function() {
	if (0==this.dt) {
		return "";
	}
	return "" + (this.dt >> 16) + "/" + ((this.dt>>8) & 0xFF) + "/" + (this.dt & 0xFF);
},
get_rating: function() {
	if (this.rating<0) {
		return "???";
	}
	return "" + this.rating;
},
get_best_time: function() {
	if (this.best<=0) {
		return "";
	}
	var ret = "" + Math.floor(this.best/60) + ":";
	var sec = this.best%60;
	if (sec>9) {
		ret+= sec;
	} else {
		ret+= "0" + sec;
	}
	return ret;
},
get_avg_time: function() {
	if (this.count<=0) {
		return "";
	}
	var tm0 = Math.round(this.sum/this.count);
	var ret = "" + Math.floor(tm0/60) + ":";
	var sec = tm0%60;
	if (sec>9) {
		ret+= sec;
	} else {
		ret+= "0" + sec;
	}
	return ret;
},
__sentinel: 0
};

var griddb = {
conn: null,
init_data: [
		[ "000050263006008107010240005500070300008605700007030008600023080802100500431080000", 32 ],
		[ "004051260000300107010246005500070010048010720060030008600423080802007000031580600", 32 ],
		[ "500400009800023600010950302200030860040000090098010007903046070005870006100005008", 32 ],
		[ "904700260200008107010240000020870016000605000160032050000023080802100004031009602", 32 ],
		[ "209004500800253009050900002000620931020000040395018000900006050600572008008100306", 33 ],
		[ "980001060000308107010240805520070300008605700007030058605023080802107000030500072", 33 ],
		[ "984701060200008000010240000020870300048605720007032050000023080000100004030509672", 33 ],
		[ "904700060200008047010040800000870306008605700107032000005020080890100004030009602", 34 ],
		[ "900001060000308107710240000520070016040605020160030058000023081802107000030500002", 35 ],
		[ "904001060200008107010240000020070316008000700167030050000023080802100004030500602", 35 ],
		[ "004001200006308007010240000020070316048010720167030050000023080800107500001500600", 36 ],
		[ "009004010800203609050060002080000900026307840005000060900030050604502008070100300", 36 ],
		[ "069700503800003670050900002000020031020307040390010000900006050034500008508009320", 36 ],
		[ "900000260000098107010200005020074016008605700160930050600003080802160000031000002", 36 ],
		[ "904001263206098000010200000520070000008605700000030058000003080000160504431500602", 36 ],
		[ "904050060200308040010200005009070016048000720160030400600003080090107004030080602", 36 ],
		[ "980700200200000047010240000000870306040605020107032000000023080890000004001009072", 36 ],
		[ "984000000000008107010240800500070310048605720067030008005023080802100000000000672", 36 ],
		[ "080700060200098000010040805020870300048605720007032050605020080000160004030009070", 37 ],
		[ "084700000250000100710240000020870300048605720007032050000023081002000034000009670", 37 ],
		[ "002000710800123605010050002000700864006502100398004000900040070405871006067000400", 38 ],
		[ "069004010800250600050001002080000001020397040300000060900800050004072008070100320", 38 ],
		[ "302800970875002000090000300900630800006907400003084006008000030000100628037008509", 38 ],
		[ "900700060206008107010040890020870000008605700000032050075020080802100504030009002", 39 ],
		[ "502000700000103000010950300250730060040000090090014057003046070000801000007000408", 40 ],
		[ "069004010800053670000900002400600930020090040095008007900006000034570008070100320", 41 ],
		[ "904001060000308100710240000509070010040000020060030408000023081002107000030500602", 42 ],
		[ "084000003000008100010200895500070000048605720000030008675003080002100000400000670", 43 ],
		[ "209000500040203670050900400000020001006397800300010000002006050034502090008000306", 43 ],
		[ "980700060200000100010046805000870010008605700060032000605420080002000004030009072", 43 ],
		[ "980001060200308100010040005000800310040010020067002000600020080002107004030500072", 44 ],
		[ "084700060200000007010240805520070000008605700000030058605023080800000004030009670", 45 ],
		[ "532400700800003605010950000200700860006000100098004007000046070405800006007005428", 45 ],
		[ "000004010801253609050900400400000931000307000395000007002006050604572108070100000", 46 ],
		[ "009700500000003600050960082080020931006307800395010060910036050004500000008009300", 48 ],
		[ "069004500801250600050900000400600930020000040095008007000006050004072108008100320", 48 ],
		[ "200704010801003609050000400400000031006000800390000007002000050604500108070109006", 48 ],
		[ "502400700800003040014950000200000060046000190090000007000046570020800006007005408", 49 ],
		[ "984001060000308000700040800020070016008000700160030050005020001000107000030500672", 49 ],
		[ "502400000809003040010950302000039060700000003090610000903046070020800906000005408", 50 ],
		[ "069700500000003600700060002480605030020090040090408067900030004004500000008009320", 56 ],
		[ "009084013801200600050060002400005930020000040095400007900030050004002108570140300", 59 ],
		[ "302006000800000104090740302900030850000907000013080006208059030409000008000200509", 59 ],
		[ "904700060250000107010040800020870000000605000000032050005020080802000034030009602", 59 ],
		[ "009000010800203670700900400400020001006397800300010007002006004034502008070000300", 65 ],
		[ "602401000000600407074030001060080070003705600080060050800040730107008000000507802", 70 ],
		[ "602400080300000407070800201000980074000705000780064000809006030107000006030007802", 74 ],
		[ "690001500000000407000830001060900074003705600780004050800046000107000000006500012", 74 ],
		[ "602001083000052400070030200200080074043000620780060009009040030007320000430500802", 81 ],
		[ "008007009300090500067400002600500470000304000045006001500008720002060005700900100", 80 ],
		[ "058200049300001007000400300030020008070304050200070030006008000400700005780002160", 71 ],
		[ "008000040300601080060400002630020008000304000200070031500008020010703005080000100", 80 ],
		[ "008000009300601000060480302600500070009304200040006001506048020000703005700000100", 89 ],
		[ "100030040300091500060000302600500070009314200040006001506000020002760005080050004", 86 ],
		[ "000007640300000087060400002600500470009304200045006001500008020410000005083900000", 76 ],
		[ "000030009304091507060400302601500070070000050040006901506008020402760805700050000", 71 ],
		[ "008207000300001000067400300000020478079304250245070000006008720000700005000902100", 72 ],
		[ "058037040324090500060400000600000078009000200240000001000008020002060895080950160", 79 ],
		[ "008200040300091500060005002030000408070010050205000030500100020002760005080002100", 88 ],
		[ "000007040300600580007080302600500070079000250040006001506040700012003005080900000", 93 ],
		[ "108007640304000000060480300030500078009000200240006030006048020000000805083900104", 71 ],
		[ "008000640300090500067400000030520008000304000200076030000008720002060005083000100", 77 ],
		[ "100207040000091007000480302000000408079000250205000000506048000400760000080902004", 74 ],
		[ "000207649000091580000400002030020008009304200200070030500008000012760000783902000", 80 ],
		[ "150000049000001500060080002030500408000304000205006030500040020002700000780000064", 77 ]

	],
init: function() {
	var file = Components.classes["@mozilla.org/file/directory_service;1"]
                     .getService(Components.interfaces.nsIProperties)
                     .get("ProfD", Components.interfaces.nsIFile);
	file.append("grids.sqlite");

	var storageService = Components.classes["@mozilla.org/storage/service;1"]
                        .getService(Components.interfaces.mozIStorageService);
	var createdb = 0;
	try {
		this.conn = storageService.openDatabase(file);
		// check whether we have got the tables
		var statement = this.conn.createStatement("SELECT count(*) FROM sqlite_master WHERE type='table' and name='grids' ");
		while (statement.executeStep()) {
			var value = statement.getInt32(0); // use the correct function!
			if (!value) {
				// initialize the database
				createdb = 1;
			}
		}
	} catch(e) {
		LOG("Exception: " + e);
		createdb = 1;
		file.remove(false);
		this.conn = storageService.openDatabase(file);
	}
	if (createdb) {
		this.createdb();
	}
},
has_grid: function(grid) {
	var statement = this.conn.createStatement("SELECT id FROM grids WHERE grid=?1");
	statement.bindStringParameter(0, grid);
	if (statement.executeStep()) {
		return statement.getInt32(0);
	}
	return null;
},
addgrid: function(grid, rating, gid) {
	var id = griddb.has_grid(grid);
	if (null!=id) {
		LOG("Already have this grid: " + id);
		return id;
	}
	var statement = this.conn.createStatement("INSERT INTO grids (id, gid, grid, rating) values (NULL, ?1, ?2, ?3)");
	if (null==gid) {
		statement.bindNullParameter(0);
	} else {
		statement.bindInt32Parameter(0, gid);
	}
	statement.bindStringParameter(1, grid);
	statement.bindInt32Parameter(2, rating);
	statement.execute();
	var id = this.conn.lastInsertRowID;
	LOG("INSERTED: " + id);
	return id;
},
createdb: function() {
	LOG("Creating griddb");
	/**
	 * id		the local grid id.
	 * gid		the global (server) grid id. null if imported grid.
	 * grid		the grid definition
	 * rating	1-99, 99 is the most difficult
	 * date		the date of the last time it was finished, 0 meaning never
	 *	 		day = date & 0xFF, 
	 *			month = date>>8 & ff, 
	 *			year = date>>16 & 0xFFFF
	 * best		the best time in s
	 * sum		the sum of times in s
	 * count	how many times it has been tried
	 */
	this.conn.executeSimpleSQL("CREATE TABLE grids (id INTEGER PRIMARY KEY, gid INTEGER, grid TEXT UNIQUE, rating INTEGER, date INTEGER, best INTEGER, sum INTEGER, count INTEGER)");
	var i = 1;
	var s1 = this.conn.createStatement("INSERT INTO grids (id, gid, grid, rating, date, best, sum, count) values (?1, NULL, ?2, ?3, 0, 0, 0, 0)");
	for (var i = 0;i<griddb.init_data.length;i++) {
		s1.bindInt32Parameter(0, i+1);
		s1.bindStringParameter(1, griddb.init_data[i][0]);
		s1.bindInt32Parameter(2, griddb.init_data[i][1]);
		s1.execute();
	}
	LOG("Creating griddb - done");
},
get_unfinished_grid: function(difficulty) {
	var s1;
	if (played) {
		s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids WHERE rating between ?1 AND ?2 AND count>0 LIMIT 1");
	} else {
		s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids WHERE rating between ?1 AND ?2 AND 0=count LIMIT 1");
	}
	if (difficulty<=1) {
		s1.bindInt32Parameter(0, 0);
		s1.bindInt32Parameter(1, 50);
	} else if (2==difficulty) {
		s1.bindInt32Parameter(0, 51);
		s1.bindInt32Parameter(1, 60);
	} else {
		s1.bindInt32Parameter(0, 61);
		s1.bindInt32Parameter(1, 1000);
	}
	var ret = new Object();
	if (s1.executeStep()) {
		return new GRID(
			s1.getInt32(0),
			s1.getInt32(1),
			s1.getString(2),
			s1.getInt32(3),
			s1.getInt32(4),
			s1.getInt32(5),
			s1.getInt32(6),
			s1.getInt32(7));
	}
	return new GRID(
		null, null,
		"502400000809003040010950302000039060700000003090610000903046070020800906000005408",
		50, 0, 0, 0, 0);
},
get_grids: function() {
	var ret = [];
	var s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids ORDER BY date");
	while (s1.executeStep()) {
		ret.push(new GRID(
			s1.getInt32(0),
			s1.getInt32(1),
			s1.getString(2),
			s1.getInt32(3),
			s1.getInt32(4),
			s1.getInt32(5),
			s1.getInt32(6),
			s1.getInt32(7)
		));
	}
	return ret;
},
get_grid_by_id: function(i) {
	var ret = [];
	var s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids WHERE id=?1");
	s1.bindInt32Parameter(0, i)
	while (s1.executeStep()) {
		return new GRID(
			s1.getInt32(0),
			s1.getInt32(1),
			s1.getString(2),
			s1.getInt32(3),
			s1.getInt32(4),
			s1.getInt32(5),
			s1.getInt32(6),
			s1.getInt32(7)
		);
	}
	return null;
},
get_grid_by_pos: function(i) {
	var s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids ORDER BY id LIMIT 1 OFFSET ?1");
	s1.bindInt32Parameter(0, i)
	while (s1.executeStep()) {
		return new GRID(
			s1.getInt32(0),
			s1.getInt32(1),
			s1.getString(2),
			s1.getInt32(3),
			s1.getInt32(4),
			s1.getInt32(5),
			s1.getInt32(6),
			s1.getInt32(7)
		);
	}
	return null;
},
get_random_grid: function(i) {
	var s1 = this.conn.createStatement("SELECT COUNT(*) FROM grids");
	if (s1.executeStep()) {
		var count = s1.getInt32(0);
		var r = Math.floor(Math.random()*count);
		return this.get_grid_by_pos(r);
	}
	return new GRID(
		null, null,
		"502400000809003040010950302000039060700000003090610000903046070020800906000005408",
		50, 0, 0, 0, 0);
},
/**
 * g is the grid
 * gid is the gid if known, null if this grid is not free, and -1 if the grid can be submitted to the server.
 */
introduce: function(g, gid) {
	g = g.replace(/[^0-9]/g, '0');
	var id = griddb.has_grid(g);
	if (null==id) {
		id = griddb.addgrid(g, -1, gid);
	}
	var s1 = this.conn.createStatement("SELECT id, gid, grid, rating, date, best, sum, count FROM grids WHERE id=?1");
	s1.bindInt32Parameter(0, id);
	if (s1.executeStep()) {
		return new GRID(
			s1.getInt32(0),
			s1.getInt32(1),
			s1.getString(2),
			s1.getInt32(3),
			s1.getInt32(4),
			s1.getInt32(5),
			s1.getInt32(6),
			s1.getInt32(7));
	}
	return new GRID(null, null, gid, 50, 0, 0, 0, 0);
},
add_score: function(grid, secs) {
	LOG("GRID before:" + grid.id + " count:" + grid.count + " sum:" + grid.sum);
	grid.sum+= secs;
	grid.count++;
	if (0==grid.best || grid.best>secs) {
		grid.best = secs;
	}
	grid.set_date();
	LOG("GRID after:" + grid.id + " count:" + grid.count + " sum:" + grid.sum);
	var s1 = this.conn.createStatement("UPDATE grids SET date=?1, best=?2, sum=?3, count=?4 WHERE id=?5");
	s1.bindInt32Parameter(0, grid.dt);
	s1.bindInt32Parameter(1, grid.best);
	s1.bindInt32Parameter(2, grid.sum);
	s1.bindInt32Parameter(3, grid.count);
	s1.bindInt32Parameter(4, grid.id);
	LOG("UPDATING:" +  grid.id);
	s1.execute();
},
__sentinel: 0 //so that we can write a , after every bloody member.
};

griddb.init();

